package com.psytech.app.task.driver.task.impl.stm;

import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;

import org.apache.commons.configuration.Configuration;
import org.apache.commons.configuration.ConfigurationException;
import org.apache.commons.configuration.PropertiesConfiguration;

import au.com.bytecode.opencsv.CSVReader;

import com.psytech.app.task.web_ui.Comment;
import com.psytech.app.task.web_ui.Task;
import com.psytech.app.task.web_ui.User;
import com.psytech.app.task.web_ui.Task.PRIORITY;
import com.psytech.app.task.web_ui.Task.STATUS;

public class DataLoader {

	String driver;
	String url;
	String login;
	String password;

	HashMap<Integer, Task> tasks = new HashMap<Integer, Task>();
	HashMap<Integer, Comment> comments = new HashMap<Integer, Comment>();
	HashMap<Integer, User> users = new HashMap<Integer, User>();

	public DataLoader(final Configuration conf) throws ClassNotFoundException {
		driver = conf.getString("spring.beanProperties.mainDataSource.driverClassName");
		url = conf.getString("spring.beanProperties.mainDataSource.url");
		login = conf.getString("spring.beanProperties.mainDataSource.userName");
		password = conf.getString("spring.beanProperties.mainDataSource.password");
		Class.forName(driver);
	}

	public void loadData() throws IOException, SQLException {
		loadUsers();
		loadTasks();
		loadTodo();
		loadComment();
	}

	private void loadUsers() throws IOException, SQLException {
		CSVReader reader = new CSVReader(new FileReader(DataLoader.class.getResource("./user.csv").getFile()), ';');
		Connection connection = DriverManager.getConnection(url, login, password);
		PreparedStatement stm = connection
				.prepareStatement("INSERT INTO user (id,uid,login,password,firstname,lastname) values (?,?,?,?,?,?)");
		String[] nextLine;
		while ((nextLine = reader.readNext()) != null) {
			User user = new User();
			stm.setInt(1, Integer.valueOf(nextLine[0]));
			stm.setString(2, nextLine[1]);
			user.setUid(nextLine[1]);
			stm.setString(3, nextLine[2]);
			user.setLogin(nextLine[2]);
			stm.setString(4, nextLine[3]);
			stm.setString(5, nextLine[4]);
			user.setEmail(nextLine[4]);
			stm.setString(6, nextLine[5]);
			users.put(Integer.valueOf(nextLine[0]), user);
			stm.executeUpdate();
		}
		stm.close();
		connection.close();
	}

	private void loadTasks() throws IOException, SQLException {
		CSVReader reader = new CSVReader(new FileReader(DataLoader.class.getResource("./tasks.csv").getFile()), ';');
		Connection connection = DriverManager.getConnection(url, login, password);
		PreparedStatement stm = connection
				.prepareStatement("INSERT INTO task (id,uid,name,description,Priority,Status,creatorId) values (?,?,?,?,?,?,?)");
		String[] nextLine;
		while ((nextLine = reader.readNext()) != null) {
			Task task = new Task();
			stm.setInt(1, Integer.valueOf(nextLine[0]));
			stm.setString(2, nextLine[1]);
			task.setUid(nextLine[1]);
			stm.setString(3, nextLine[2]);
			task.setName(nextLine[2]);
			stm.setString(4, nextLine[3]);
			task.setDescription(nextLine[3]);
			stm.setString(5, nextLine[4]);
			task.setPriority(PRIORITY.valueOf(nextLine[4]));
			stm.setString(6, nextLine[5]);
			task.setStatus(STATUS.valueOf(nextLine[5]));
			stm.setInt(7, Integer.valueOf(nextLine[6]));
			task.setCreatorUid(users.get(Integer.valueOf(nextLine[6])).getUid());
			stm.executeUpdate();
			tasks.put(Integer.valueOf(nextLine[0]), task);
		}
		stm.close();
		connection.close();
	}

	private void loadTodo() throws IOException, SQLException {
		CSVReader reader = new CSVReader(new FileReader(DataLoader.class.getResource("./todo.csv").getFile()), ';');
		Connection connection = DriverManager.getConnection(url, login, password);
		PreparedStatement stm = connection.prepareStatement("INSERT INTO todo (idUser,idTask) values (?,?)");
		String[] nextLine;
		while ((nextLine = reader.readNext()) != null) {
			for (int i = 0; i < nextLine.length; i++) {
				stm.setInt(i + 1, Integer.valueOf(nextLine[i]));
			}
			stm.executeUpdate();
		}
		stm.close();
		connection.close();
	}

	private void loadComment() throws IOException, SQLException {
		CSVReader reader = new CSVReader(new FileReader(DataLoader.class.getResource("./comment.csv").getFile()), ';');
		Connection connection = DriverManager.getConnection(url, login, password);
		PreparedStatement stm = connection
				.prepareStatement("INSERT INTO comment (id,uid,text,taskId,writerId,creationDate) values (?,?,?,?,?,CURDATE())");
		String[] nextLine;
		while ((nextLine = reader.readNext()) != null) {
			Comment comment = new Comment();
			stm.setInt(1, Integer.valueOf(nextLine[0]));
			stm.setString(2, nextLine[1]);
			comment.setUid(nextLine[1]);
			stm.setString(3, nextLine[2]);
			comment.setText(nextLine[2]);
			stm.setInt(4, Integer.valueOf(nextLine[3]));
			comment.setTaskUid(tasks.get(Integer.valueOf(nextLine[3])).getUid());
			stm.setInt(5, Integer.valueOf(nextLine[4]));
			comment.setWriterUid(users.get(Integer.valueOf(nextLine[4])).getUid());
			comments.put(Integer.valueOf(nextLine[0]), comment);
			stm.executeUpdate();
		}
		stm.close();
		connection.close();
	}

	public void truncateData() throws IOException, SQLException {
		Connection connection = DriverManager.getConnection(url, login, password);
		connection.createStatement().execute("truncate table todo");
		connection.createStatement().execute("truncate table comment");
		connection.createStatement().execute("truncate table task");
		connection.createStatement().execute("truncate table user");

	}

	public static void main(final String[] args) throws IOException, ConfigurationException, ClassNotFoundException, SQLException {
		URL url = DataLoader.class.getResource("./com.psytech.app.task.configFile.properties");
		Configuration conf = new PropertiesConfiguration(url);
		DataLoader dl = new DataLoader(conf);
		dl.truncateData();
		dl.loadUsers();
		dl.loadTasks();
		dl.loadTodo();
	}

	public HashMap<Integer, Task> getTasks() {
		return tasks;
	}

	public HashMap<Integer, Comment> getComments() {
		return comments;
	}

	public HashMap<Integer, User> getUsers() {
		return users;
	}

}
